/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package fplaton.economics;

import fplaton.db.SQLiteJDBC;
import static fplaton.helper.DbChecking.tableIsEmpty;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Konstantinos
 */
public class EconomicsDao implements IEconomicsDao {

    private SQLiteJDBC economicsTableCreation() {
        SQLiteJDBC connection = new SQLiteJDBC("frontistirio.db");
        try {
            String create_table = "CREATE TABLE IF NOT EXISTS ECONOMICS "
                    + "(ARXIKIMEIWSI    PRIMARY KEY    NOT NULL,"
                    + " MEIWSIANAPAIDI    INTEGER      NOT NULL,"
                    + " KOSTOSGUMN1MATH    FLOAT       NOT NULL,"
                    + " KOSTOSGUMN2MATH    FLOAT       NOT NULL,"
                    + " KOSTOSGUMN3MATH    FLOAT       NOT NULL,"
                    + " KOSTOSLUK1MATH     FLOAT       NOT NULL,"
                    + " KOSTOSLUK2MATH     FLOAT       NOT NULL,"
                    + " KOSTOSLUK3MATH     FLOAT       NOT NULL)";

            connection.execute(create_table);
        } catch (SQLException ex) {
            Logger.getLogger(EconomicsDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return connection;
    }

    private void economicsTableDataInsert(SQLiteJDBC connection, int starterCut, int cutPerKid,
            float firstGradeCost, float secondGradeCost, float thirdGradeCost,
            float fourthGradeCost, float fifthGradeCost, float sixthGradeCost) {

        try {
            PreparedStatement prep = connection.conn.prepareStatement(
                    "INSERT INTO ECONOMICS VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
            prep.setInt(1, starterCut);
            prep.setInt(2, cutPerKid);
            prep.setFloat(3, firstGradeCost);
            prep.setFloat(4, secondGradeCost);
            prep.setFloat(5, thirdGradeCost);
            prep.setFloat(6, fourthGradeCost);
            prep.setFloat(7, fifthGradeCost);
            prep.setFloat(8, sixthGradeCost);
            connection.conn.setAutoCommit(false);
            prep.execute();
            connection.conn.setAutoCommit(true);
            prep.close();
            JOptionPane.showMessageDialog(null, "Οι τιμές αποθηκεύτηκαν.");

        } catch (SQLException ex) {
            Logger.getLogger(EconomicsDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        connection.closeConnection();

    }

    private boolean economicsTableDataUpdate(SQLiteJDBC connection, int starterCut, int cutPerKid,
            float firstGradeCost, float secondGradeCost, float thirdGradeCost,
            float fourthGradeCost, float fifthGradeCost, float sixthGradeCost) {

        try {
            PreparedStatement prep = connection.conn.prepareStatement(
                    "UPDATE ECONOMICS SET ARXIKIMEIWSI = ?, MEIWSIANAPAIDI = ?,"
                    + "KOSTOSGUMN1MATH = ?, KOSTOSGUMN2MATH = ?,"
                    + "KOSTOSGUMN3MATH = ?, KOSTOSLUK1MATH = ?,"
                    + "KOSTOSLUK2MATH = ?, KOSTOSLUK3MATH = ?");
            prep.setInt(1, starterCut);
            prep.setInt(2, cutPerKid);
            prep.setFloat(3, firstGradeCost);
            prep.setFloat(4, secondGradeCost);
            prep.setFloat(5, thirdGradeCost);
            prep.setFloat(6, fourthGradeCost);
            prep.setFloat(7, fifthGradeCost);
            prep.setFloat(8, sixthGradeCost);
            connection.conn.setAutoCommit(false);
            prep.executeUpdate();
            connection.conn.setAutoCommit(true);
            prep.close();
            JOptionPane.showMessageDialog(null, "Οι τιμές αποθηκεύτηκαν.");

        } catch (SQLException ex) {
            Logger.getLogger(EconomicsDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        connection.closeConnection();

        return true;
    }

    @Override
    public boolean insertOrUpdateEconomicsTable(int starterCut, int cutPerKid,
            float firstGradeCost, float secondGradeCost, float thirdGradeCost,
            float fourthGradeCost, float fifthGradeCost, float sixthGradeCost,
            databaseEconomicsSectionController databaseEconomicsSectionController) throws SQLException {

        try {
        SQLiteJDBC connection = economicsTableCreation();
        ResultSet economicsTableData;
        economicsTableData = connection.executeQuery("SELECT * FROM ECONOMICS");
        if (tableIsEmpty(economicsTableData)) {
            economicsTableDataInsert(connection, starterCut, cutPerKid, firstGradeCost,
                    secondGradeCost, thirdGradeCost, fourthGradeCost, fifthGradeCost,
                    sixthGradeCost);
            return true;
        } else {
            economicsTableDataUpdate(connection, starterCut, cutPerKid, firstGradeCost,
                    secondGradeCost, thirdGradeCost, fourthGradeCost, fifthGradeCost,
                    sixthGradeCost);
            return true;
        }
        } catch (SQLException ex) {
            Logger.getLogger(EconomicsDao.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        

    }

}
